/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package jdbcexample;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.*;
import javax.swing.JOptionPane;
 
public class JDBCExample {
 
    private Connection connection = null;
    public void establishConnection()
    {
        System.out.println("-------- PostgreSQL "
				+ "JDBC Connection Testing ------------");
        
        if (connection != null)
            return;
       try
        {
           Class.forName("org.postgresql.Driver");
           connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/test", "abhi",
					"d3fad7d3");
           
           if (connection != null) {
               System.out.println("Connecting to database...");
           }
        } catch(Exception e){
            System.out.println("Problem when connecting to the database 1");
        }
    }

    public void closeConnection()
    {
        try
        {
            connection.close();
        }catch(Exception e)
        {
            System.out.println("Problem to close the connection to the database");
        }
    }

    public void startTx()
    {
        PreparedStatement prep = null;
        try
        {
            prep = connection.prepareStatement("begin transaction;");
            prep.executeUpdate();
            System.out.println("begin transaction");
            
        }catch(Exception e)
        {
            System.out.println("Problem in starting the transaction");
        }
    }
    
    public void setEnvironment()
    {
        PreparedStatement prep = null;
        try
        {
            prep = connection.prepareStatement("set enable_seqscan = off");
            prep.executeUpdate();
            prep = connection.prepareStatement("set enable_bitmapscan = off");
            prep.executeUpdate();
            System.out.println("Environment variable set");
            
        }catch(Exception e)
        {
            System.out.println("Problem in setting the environment variables");
        }
    }
   
    public void endTx()
    {
        PreparedStatement prep = null;
        try
        {
            prep = connection.prepareStatement("end transaction;");
            prep.executeUpdate();
            System.out.println("End transaction");
            
        }catch(Exception e)
        {
            System.out.println("Problem in closing the transaction");
        }
    }
    
    public PreparedStatement insertData(int firstInsert,int lastInsert)
    {
        PreparedStatement prep = null;
        int valueOfId;
        
        try
        {
            connection.setAutoCommit(false);
        
            for(valueOfId=firstInsert;valueOfId < lastInsert;valueOfId++)
            {
            
            prep = connection.prepareStatement("INSERT INTO emp(name,id) values(?,?);");
            prep.setInt(2, valueOfId);
            prep.setString(1, "rohit");
            prep.executeUpdate();
           // System.out.println("Insertion works fine");
        } 
            System.out.println("Insertion works fine");
            connection.commit();
        }catch(Exception e)
        {
            System.out.println("Problem in inserting the database");
        }
        return prep;
    }
	
    public ResultSet selectData(int firstSelect,int lastSelect)
    {
        ResultSet rs = null;
        Statement s = null;
        PreparedStatement prep = null;
        
        try
        {
            connection.setAutoCommit(false);
        
            int id =firstSelect;
            String selectQuery= "SELECT * FROM emp where id = " + Integer.toString(id);
            //String selectQuery= "SELECT * FROM emp";
            Statement st = connection.createStatement();
            rs=st.executeQuery(selectQuery);
            System.out.println("Selection works fine");
            connection.commit();
        }catch(Exception e)
        {
            System.out.println("Problem in searching the database");
        }
        
        return rs;
    }
    
    public PreparedStatement updateData(int firstUpdate, int lastUpdate)
    {
        ResultSet rs = null;
        Statement s = null;
        PreparedStatement prep = null;
        int valueOfId;
        try
        {
            connection.setAutoCommit(false);
        
            for(valueOfId=firstUpdate;valueOfId < lastUpdate;valueOfId++)
            {
              String updateSql= "UPDATE emp SET name = 'goyal' where id = " + Integer.toString(valueOfId);
              Statement st=connection.createStatement();
              st.executeUpdate(updateSql);
              
        } 
            System.out.println("Update works fine");
            connection.commit();
        }catch(Exception e)
        {
            System.out.println("Problem in updating the database");
        }
        return prep;
    }
    public static void main(String[] argv) {
 
        long startTime=System.currentTimeMillis();
        PreparedStatement prep = null;
        String string = "";
        ResultSet rs=null;
        JDBCExample jDBCExample=new JDBCExample();
        jDBCExample.establishConnection();
        prep=jDBCExample.insertData(100,5000);
        rs=jDBCExample.selectData(2140,2140);
        
        prep=jDBCExample.insertData(5000,10000);
        rs=jDBCExample.selectData(8140,8140);
        
        prep=jDBCExample.insertData(10000,15000);
        prep=jDBCExample.updateData(10000,11000);
        rs=jDBCExample.selectData(10630,10630);
        
        prep=jDBCExample.insertData(15000,18000);
        rs=jDBCExample.selectData(17840,17840);
        
        jDBCExample.closeConnection();
        
        long stopTime = System.currentTimeMillis();
        long elapsedTime = stopTime - startTime;
        System.out.println("Total Execution time is "+ elapsedTime);
    }
}